import streamlit as st
import pandas as pd
from db_utils import get_connection
import plotly.express as px

def show_reports():
    st.header("报表与统计分析")

    conn = get_connection()
    if not conn:
        return

    try:
        cursor = conn.cursor(dictionary=True)
        
        tab_monthly, tab_card_summary = st.tabs([
            "月度交易汇总", "银行卡交易额统计"
        ])

        with tab_monthly:
            st.subheader("月度交易汇总")
            
            query = """
                SELECT 
                    DATE_FORMAT(tradeDate, '%Y-%m') as month,
                    tradeType,
                    COUNT(*) as transaction_count,
                    SUM(tradeMoney) as total_amount
                FROM tradeInfo
                GROUP BY month, tradeType
                ORDER BY month DESC, tradeType;
            """
            cursor.execute(query)
            data = cursor.fetchall()

            if data:
                df = pd.DataFrame(data)
                
                # 数据透视，使'存入'和'支取'成为列
                pivot_df = df.pivot_table(
                    index='month', 
                    columns='tradeType', 
                    values='total_amount',
                    aggfunc='sum'
                ).fillna(0)
                
                # 计算总计和净值
                if '存入' not in pivot_df.columns: pivot_df['存入'] = 0
                if '支取' not in pivot_df.columns: pivot_df['支取'] = 0
                
                # 将Decimal类型转换为float以支持绘图和样式
                pivot_df['存入'] = pivot_df['存入'].astype(float)
                pivot_df['支取'] = pivot_df['支取'].astype(float)
                
                pivot_df['总交易额'] = pivot_df['存入'] + pivot_df['支取']
                pivot_df['资金净流动'] = pivot_df['存入'] - pivot_df['支取']
                
                st.dataframe(pivot_df.style.format("{:,.2f}元").background_gradient(cmap='viridis'))

                # 可视化图表
                st.subheader("资金流动趋势图")
                fig = px.bar(
                    pivot_df, 
                    y=['存入', '支取'], 
                    barmode='group',
                    title="月度存取款对比",
                    labels={'value': '金额(元)', 'month': '月份'}
                )
                st.plotly_chart(fig, use_container_width=True)
                
                # 导出
                csv = pivot_df.to_csv().encode('utf-8')
                st.download_button(
                    label="导出月度汇总报表为CSV",
                    data=csv,
                    file_name='monthly_summary_report.csv',
                    mime='text/csv',
                )
            else:
                st.info("系统中没有交易记录可供分析。")

        with tab_card_summary:
            st.subheader("银行卡交易额统计")
            
            query_card = """
                SELECT 
                    t.cardID, 
                    u.customerName, 
                    SUM(CASE WHEN t.tradeType = '存入' THEN t.tradeMoney ELSE 0 END) as total_deposit, 
                    SUM(CASE WHEN t.tradeType = '支取' THEN t.tradeMoney ELSE 0 END) as total_withdrawal 
                FROM tradeInfo t 
                JOIN cardInfo c ON t.cardID = c.cardID 
                JOIN userInfo u ON c.customerID = u.customerID 
                GROUP BY t.cardID, u.customerName
                ORDER BY total_deposit DESC, total_withdrawal DESC;
            """
            cursor.execute(query_card)
            card_data = cursor.fetchall()
            
            if card_data:
                df_card = pd.DataFrame(card_data)
                
                # 将Decimal类型转换为float以支持绘图和样式
                df_card['total_deposit'] = df_card['total_deposit'].astype(float)
                df_card['total_withdrawal'] = df_card['total_withdrawal'].astype(float)

                df_card['总交易额'] = df_card['total_deposit'] + df_card['total_withdrawal']
                df_card['资金净流入'] = df_card['total_deposit'] - df_card['total_withdrawal']

                st.dataframe(
                    df_card.style.format({
                        'total_deposit': "{:,.2f}元",
                        'total_withdrawal': "{:,.2f}元",
                        '总交易额': "{:,.2f}元",
                        '资金净流入': "{:,.2f}元"
                    }).bar(subset=['total_deposit', 'total_withdrawal'], color=['#55A868', '#C44E52'])
                )
                
                # 导出
                csv_card = df_card.to_csv(index=False).encode('utf-8')
                st.download_button(
                    label="导出银行卡统计报表为CSV",
                    data=csv_card,
                    file_name='card_summary_report.csv',
                    mime='text/csv',
                    key='download_card_csv'
                )
            else:
                st.info("系统中没有交易记录可供分析。")
            
    except Exception as e:
        st.error(f"生成报表时发生错误: {e}")
    finally:
        if conn.is_connected():
            cursor.close()
            conn.close() 